package database.customer;

import database.CRUD;
import database.GUImanager;
import database.Redis;
import database.employee.tb_employee;
import redis.clients.jedis.Jedis;
import javax.swing.*;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

import static javax.swing.WindowConstants.DISPOSE_ON_CLOSE;
import static javax.swing.WindowConstants.EXIT_ON_CLOSE;

public class CustomerCURDGUI {

    private DefaultTableModel model;
    private JTable table;
    JFrame framecustomer;
    public CustomerCURDGUI(tb_employee e1) throws SQLException {
        framecustomer=new JFrame();
        DefaultTableModel model;
        table = new JTable();
        table.setRowSelectionAllowed(true); // 允许通过点击选择行
        ListSelectionModel selectionModel = table.getSelectionModel();
        selectionModel.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); // 或者使用多选 ListSelectionModel.MULTIPLE_INTERVAL_SELECTION
        DefaultTableModel tableModel;
        int selectedRow; // 用于记录选中的行

        // 数据库连接信息
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
        Statement stmt = conn.createStatement();
        String query = "SELECT * FROM " + "tb_customer";
        ResultSet rs = stmt.executeQuery(query);
        framecustomer.setTitle("超市管理系统");
        framecustomer.setBounds(100,100,800,600);
        framecustomer.setDefaultCloseOperation(DISPOSE_ON_CLOSE); // 设置默认关闭操作
        framecustomer.setLocationRelativeTo(null); // 居中显示

        // Get metadata from ResultSet
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            columnNames[i] = metaData.getColumnName(i + 1); // Columns in ResultSet start from index 1
        }

        // Create table model with columns from metadata
        model = new DefaultTableModel(columnNames, 0); // 0 rows initially
        table.getModel().addTableModelListener(new TableModelListener() {
            @Override
            public void tableChanged(TableModelEvent e) {
                if (e.getType() == TableModelEvent.UPDATE && e.getColumn() != -1) {
                    int rowIndex = e.getFirstRow();
                    int columnIndex = e.getColumn();
                    Object value = model.getValueAt(rowIndex, columnIndex);
                    // 检查单元格是否是新值，且不是主键列
                    if (rowIndex == table.getSelectedRow()) {
                        updateSelectedRow1();
                    }
                }
            }
        });
        // Populate table model with data from ResultSet
        while (rs.next()) {
            Object[] rowData = new Object[columnCount];
            for (int i = 0; i < columnCount; i++) {
                rowData[i] = rs.getObject(i + 1); // ResultSet columns start from index 1
            }
            model.addRow(rowData);
        }

        // Create JTable with the model
        table = new JTable(model);


        // Create a scroll pane for the table
        JScrollPane scrollPane = new JScrollPane(table);
        framecustomer.add(scrollPane, BorderLayout.CENTER);

        // Panel for buttons
        JPanel buttonPanel = new JPanel();
        JButton addButton = new JButton("新增");
        addButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    openAddDialog1(e1);
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        });
        buttonPanel.add(addButton);

        JButton deleteButton = new JButton("删除");
        deleteButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                deleteSelectedRows();
            }
        });
        buttonPanel.add(deleteButton);

        JButton updateButton = new JButton("更新");
        updateButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                updateSelectedRow1();
            }
        });
        buttonPanel.add(updateButton);
        JButton ExportButton = new JButton("导出");
        ExportButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                exportSelectedRowToCsv(table);
            }
        });
        buttonPanel.add(ExportButton);
        JButton returnButton =new JButton("返回");
        returnButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                new GUImanager(e1);
                new Redis("tb_customer");
                framecustomer.dispose();
            }
        });
        buttonPanel.add(returnButton);

        framecustomer.add(buttonPanel, BorderLayout.SOUTH);

        framecustomer.setVisible(true);
    }

    private void openAddDialog1(tb_employee e1) throws SQLException {
        framecustomer.dispose();
        JFrame addFrame = new JFrame("添加新客户");
        addFrame.setSize(800, 600);
        addFrame.setDefaultCloseOperation(DISPOSE_ON_CLOSE);
        addFrame.setLocationRelativeTo(null); // 居中显示
        JPanel addPanel = new JPanel(new BorderLayout());
        CRUD crud = new CRUD(); // Assuming CRUD class handles database operations
        Connection conn = crud.getConnection();
        Statement stmt = conn.createStatement();
        String query = "SELECT * FROM " + "tb_customer";
        ResultSet rs = stmt.executeQuery(query);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            columnNames[i] = metaData.getColumnName(i + 1); // Columns in ResultSet start from index 1
        }

// Create DefaultTableModel with retrieved column names
        DefaultTableModel addTableModel = new DefaultTableModel(columnNames, 0); // 0 rows initially

// Example: Initialize a JTable and JScrollPane with the DefaultTableModel
        JTable addTable = new JTable(addTableModel);
        JScrollPane addScrollPane = new JScrollPane(addTable);

        JButton addButton = new JButton("新增");
        addButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // Add a blank row to the table
                addTableModel.addRow(new Object[addTableModel.getColumnCount()]);
            }
        });

        JButton saveButton = new JButton("保存");
        saveButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    if (addTableModel.getRowCount() == 0) {
                        JOptionPane.showMessageDialog(addFrame, "表格为空，没有数据需要保存。", "提示", JOptionPane.INFORMATION_MESSAGE);
                        return; // 直接返回，不执行保存操作
                    }
                    // 构建并执行插入语句
                    for (int i = 0; i < addTableModel.getRowCount(); i++) {
                        // Prepare an SQL INSERT statement
                        StringBuilder sb = new StringBuilder("INSERT INTO tb_customer (");
                        for (int j = 0; j < addTableModel.getColumnCount(); j++) {
                            sb.append(addTableModel.getColumnName(j));
                            if (j < addTableModel.getColumnCount() - 1) {
                                sb.append(", ");
                            }
                        }
                        sb.append(") VALUES (");
                        for (int j = 0; j < addTableModel.getColumnCount(); j++) {
                            Object value = addTableModel.getValueAt(i, j);
                            // Depending on the column type in your database, you might need to handle different types here
                            if (value instanceof String) {
                                sb.append("'").append(value).append("'");
                            } else {
                                sb.append(value);
                            }
                            if (j < addTableModel.getColumnCount() - 1) {
                                sb.append(", ");
                            }
                        }
                        sb.append(")");

                        // Execute the INSERT statement
                        String insertQuery = sb.toString();
                        Statement insertStmt = conn.createStatement();
                        insertStmt.executeUpdate(insertQuery);
                        insertStmt.close();

                    }
                    JOptionPane.showMessageDialog(addFrame, "数据保存成功！");
                } catch (SQLException ex) {
                    JOptionPane.showMessageDialog(addFrame, "数据保存失败：" + ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
                    ex.printStackTrace();
                }
            }
        });


        JButton returnButton = new JButton("返回");
        returnButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                addFrame.dispose(); // Just close the addFrame without saving
                try {
                    new CustomerCURDGUI(e1);
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        });

        JPanel buttonPanel = new JPanel();
        buttonPanel.add(addButton);
        buttonPanel.add(saveButton);
        buttonPanel.add(returnButton);

        // Add listener to handle cell updates
        addTable.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseClicked(MouseEvent e) {
                int row = addTable.getSelectedRow();
                int column = addTable.getSelectedColumn();
                if (row != -1 && column != -1) {
                    Object value = addTableModel.getValueAt(row, column);
                    // You can perform any update or action with the clicked value here
                }
            }
        });

        addPanel.add(addScrollPane, BorderLayout.CENTER);
        addPanel.add(buttonPanel, BorderLayout.SOUTH);

        addFrame.add(addPanel);
        addFrame.setVisible(true);
    }


    private void deleteSelectedRows() {
        final DefaultTableModel model = (DefaultTableModel) table.getModel();
        if (model == null) {
            SwingUtilities.invokeLater(() -> showErrorMessage("表格模型未初始化。"));
            return;
        }

        int[] selectedRows = table.getSelectedRows();
        if (selectedRows.length == 0) {
            SwingUtilities.invokeLater(() -> showErrorMessage("请选择要删除的记录。"));
            return;
        }

        // 确认用户想要删除所有选中的记录
        int confirm = JOptionPane.showConfirmDialog(framecustomer,
                "确定要删除所有选中的记录吗？这将删除相关的商品记录。", "确认删除",
                JOptionPane.YES_NO_OPTION);
        if (confirm != JOptionPane.YES_OPTION) {
            return;
        }

        // 存储选中的客户编号cid
        List<String> cidsToDelete = new ArrayList<>();
        for (int rowIndex : selectedRows) {
            // 这里Cid是第一列，并且表格的行索引与模型索引相同
            String cid = model.getValueAt(rowIndex, 0).toString();
            cidsToDelete.add(cid);
        }
        new Thread(() -> {
            try (Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456")) {
                conn.setAutoCommit(false); // 开始事务

                for (String eid : cidsToDelete) {
                    try {
                        if (checkEidInMain(eid, conn)) {
                            // 删除采购明细表和采购主表中的相关记录
                            if (!deleteDetailsAndMainForEid(eid, conn)) {
                                System.out.println("删除操作失败: " + eid);
                                throw new SQLException("级联删除失败");
                            }
                        }
                        // 删除员工表中的记录
                        if (!deleteEmployee(eid, conn)) {
                            System.out.println("删除操作失败: " + eid);
                            throw new SQLException("删除员工记录失败");
                        }
                    } catch (SQLException ex) {
                        System.out.println("删除过程中发生错误: " + ex.getMessage());
                        conn.rollback(); // 发生异常，回滚事务
                        SwingUtilities.invokeLater(() -> showErrorMessage("删除操作失败，请检查控制台日志。"));
                        return;
                    }
                }

                conn.commit(); // 提交事务
                SwingUtilities.invokeLater(() -> {
                    // 更新UI
                    model.setRowCount(0);
                    showErrorMessage("所有选中记录已成功删除。");
                    model.fireTableDataChanged();
                });
            } catch (SQLException ex) {
                ex.printStackTrace();
                SwingUtilities.invokeLater(() -> showErrorMessage("数据库连接失败：" + ex.getMessage()));
            }
        }).start();
    }


    private boolean checkEidInMain(String cid, Connection conn) throws SQLException {
        // 检查商品表中是否引用了Cid
        String checkSql = "SELECT COUNT(*) FROM tb_good WHERE Cid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(checkSql)) {
            pstmt.setString(1, cid);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (!rs.next() || rs.getInt(1) == 0) {
                    return false;
                }
            }
        }
        return true;
    }

    private boolean deleteDetailsAndMainForEid(String cid, Connection conn) throws SQLException {
        // 首先删除采购明细表中的相关记录
        String deleteDetailSql = "DELETE FROM tb_pay_detail WHERE Gid IN (SELECT Gid FROM tb_good WHERE Cid = ?)";
        try (PreparedStatement deleteDetailStmt = conn.prepareStatement(deleteDetailSql)) {
            deleteDetailStmt.setString(1, cid);
            int rowsAffected = deleteDetailStmt.executeUpdate();
            if (rowsAffected == 0) {
                // 如果没有记录被删除，可能Eid没有对应的记录
                return false;
            }
        }
        // 删除商品表中的记录
        String deleteMainSql = "DELETE FROM tb_good WHERE Cid = ?";
        try (PreparedStatement deleteMainStmt = conn.prepareStatement(deleteMainSql)) {
            deleteMainStmt.setString(1, cid);
            int rowsAffected = deleteMainStmt.executeUpdate();
            if (rowsAffected == 0) {
                // 如果没有记录被删除，可能Eid没有对应的记录
                return false;
            }
        }

        // 如果所有删除操作成功，返回true
        return true;
    }

    private boolean deleteEmployee(String cid, Connection conn) throws SQLException {
        // 删除客户表中的记录
        String deleteSql = "DELETE FROM tb_customer WHERE Cid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {
            pstmt.setString(1, cid);
            return (pstmt.executeUpdate() > 0);
        }
    }

    private void showErrorMessage(String message) {
        JOptionPane.showMessageDialog(framecustomer, message, "错误", JOptionPane.ERROR_MESSAGE);
    }

    // Method to update selected row
    private void updateSelectedRow1() {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(framecustomer, "Please select a row to update.", "Update Error", JOptionPane.ERROR_MESSAGE);
            return;
        }

        DefaultTableModel model = (DefaultTableModel) table.getModel();
        StringBuilder sql = new StringBuilder("UPDATE tb_customer SET ");

        java.util.List<String> updateColumns = new ArrayList<>();//存储列名和对应占位符
        List<Object> params = new ArrayList<>();//存储与这些占位符对应的实际参数

        for (int i = 0; i < model.getColumnCount(); i++) {
            String columnName = model.getColumnName(i);//获取列名
            Object value = model.getValueAt(selectedRow, i);//获取模型中特定行 selectedRow 和列 i 的数据值。
            // 跳过主键列 "CID"
            if (!"Cid".equals(columnName)) {
                // 为更新语句添加列名
                updateColumns.add(columnName + " = ");
                // 存储参数值
                params.add(value);
            }
        }
        int i1=0;
        for(Object updateColumn:updateColumns) {
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i1);
                sql.append(updateColumn);
                sql.append("'");
                sql.append(param);
                sql.append("'");
                if(i1 != (updateColumns.size()-1)){
                    sql.append(",");
                }
                i1++;
                break;
            }
        }

        // 添加主键条件
        sql.append(" WHERE CID = ");
        sql.append("'");
        sql.append(model.getValueAt(selectedRow, 0));
        sql.append("'");
        // 主键值也作为参数传入

        System.out.println(sql.toString());
        // 执行数据库更新操作
        String finalSql = sql.toString();
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
             PreparedStatement pstmt = conn.prepareStatement(finalSql)) {

            // 执行更新操作
            int rowsUpdated = pstmt.executeUpdate();
            if (rowsUpdated > 0) {
                JOptionPane.showMessageDialog(framecustomer, "Row updated successfully.", "Update Successful", JOptionPane.INFORMATION_MESSAGE);
            } else {
                JOptionPane.showMessageDialog(framecustomer, "Failed to update row. No rows affected.", "Update Failed", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(framecustomer, "SQL Error: " + e.getMessage(), "Update Failed", JOptionPane.ERROR_MESSAGE);
            e.printStackTrace();
        }
    }


    public void exportSelectedRowToCsv(JTable table) {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请选择一个要导出的行。", "提示", JOptionPane.WARNING_MESSAGE);
            return;
        }
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String formattedDateTime = now.format(formatter);

        // 调用JFileChooser选择文件路径
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("选择CSV文件存放路径");
        int result = fileChooser.showSaveDialog(null);
        if (result == JFileChooser.APPROVE_OPTION) {
            File selectedFile = fileChooser.getSelectedFile();
            String csvFilePath = selectedFile.getAbsolutePath();

            // 确保文件名以.csv结尾
            if (!csvFilePath.endsWith(".csv")) {
                csvFilePath += ".csv";
            }

            try (OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(csvFilePath), "UTF-8");
                 PrintWriter pw = new PrintWriter(osw)) {

                TableModel model = table.getModel();
                int columnCount = model.getColumnCount();

                // 写入CSV文件数据
                for (int column = 0; column < columnCount; column++) {
                    Object value = model.getValueAt(selectedRow, column);
                    // 将值转换为字符串，并确保字符串编码为UTF-8
                    String stringValue = (value == null) ? "" : value.toString();
                    pw.print(stringValue);
                    if (column < columnCount - 1) {
                        pw.print(",");
                    }
                }
                pw.println();

                JOptionPane.showMessageDialog(null, "数据导出成功，文件已保存到：" + csvFilePath);
            } catch (IOException e) {
                JOptionPane.showMessageDialog(null, "导出失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
                e.printStackTrace();
            }
        } else {
            System.out.println("文件保存操作被取消");
        }
    }

    /*public static void main(String[] args) {
        // Example usage:
        SwingUtilities.invokeLater(() -> {
            try {
                CustomerCURDGUI d1 = new CustomerCURDGUI();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }*/
}

